//
//  SQLiteTool.swift
//  FMDBTest
//
//  Created by 劢克科技 on 2019/2/14.
//  Copyright © 2019 劢克科技. All rights reserved.
//

import UIKit
import FMDB

class SQLiteTool: NSObject {
    
    static let shared = SQLiteTool()
    
    lazy var db: FMDatabase = {
//        let documentPath =    NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).first!
//        let realPath = (documentPath as NSString).appendingPathComponent("test.sqlite")
        let realPath = "/Users/meckey/Desktop/data/test.sqlite"
        let db = FMDatabase(path: realPath)
        return db
    }()
    
    lazy var dbQueue: FMDatabaseQueue = {
        return FMDatabaseQueue(path: "/Users/meckey/Desktop/data/test.sqlite")!
    }()
    
    override init() {
        super.init()
        
        if !db.open() {
            print("数据库打开失败")
        }
        createTable()
    }
    
    func createTable() {
        let sql = "create table if not exists t_stu (id integer primary key autoincrement, name text not null, age integer, score real default 60)"
//        let res = db.executeUpdate(sql, withArgumentsIn: [1])
//
//        if res {
//            print("s创建成功")
//        }
        
        dbQueue.inDatabase { (db: FMDatabase) in
            let res = db.executeUpdate(sql, withArgumentsIn: [1])
            
            if res {
                print("s创建成功")
            }
        }
    }
    
    func insertRecord() {
        do {
            let sql = "insert into t_stu (name,age,score) values ('李四',22,  88.5)"
            try db.executeUpdate(sql, values: nil)
        } catch let err {
            print(err)
        }
    }
    
    func get() {
        do {
            let sql = "select * from t_stu"
            let res = try db.executeQuery(sql, values: nil)
            while res.next() {
                let name = res.string(forColumn: "name")
                let age = res.int(forColumn: "age")
                let score = res.double(forColumn: "score")
                print(name, age, score)
            }
        } catch let err {
            print(err)
        }
    }
    
    func queryStatements() {
        let sql = "insert into t_stu (name,age,score) values ('李四',22,  88.5);insert into t_stu (name,age,score) values ('李四2',223,  881.5);insert into t_stu (name,age,score) values ('李四',22,  88.5);insert into t_stu (name,age,score) values ('李四2',223,  881.5)"
        let res = db.executeStatements(sql)
        if res {
            print("多语句执行成功")
        }
    }
}
